import asyncio
from typing import List, Dict, Any, Literal
from db.operations import BaseOperations, MainCategoryOperations, SubCategoryOperations


class BookOperations(BaseOperations):
    """图书信息数据库操作类"""

    def __init__(self):
        # 继承BaseOperations，指定表名为books
        super().__init__("books")

    async def batch_insert_books(
        self,
        books_data: List[Dict[str, Any]],
        on_duplicate: Literal["skip", "update"] = "skip",
    ) -> int:
        """
        批量插入图书数据

        Args:
            books_data (List[Dict[str, Any]]): 图书数据列表
            每个字典应包含图书的字段，例如：
            {
                "book_id": 1,
                "title": "书名",
                "author": "作者",
                "translator": "译者",
                "language": "中文",
                "intro": "图书简介...",
                "cover_url": "http://...",
                "good_rating": 0,
                "fair_rating": 0,
                "poor_rating": 0,
                "rating": 0.0,
                "rating_count": 0,
                "total_chapters": 20,
                "price": 29.8,
                "publish_time": "2024-01-01",
                "reading_count": 0,
            }
        on_duplicate (str): 重复处理策略 - "skip"(跳过), "update"(更新)

        Returns:
            int: 成功插入的图书数量
        """
        if not books_data:
            return 0

        # 调用父类的批量插入方法
        return await self.batch_insert(books_data, on_duplicate=on_duplicate)

    async def batch_query_books_by_ids(
        self, book_ids: List[int], order_by: str = "book_id ASC"
    ) -> List[Dict[str, Any]]:
        """
        根据批量 book_id 查询图书信息

        Args:
            book_ids (List[int]): 图书ID列表
            order_by (str): 排序子句，默认按 book_id 升序排列

        Returns:
            List[Dict[str, Any]]: 查询到的图书信息列表
            每个字典应包含图书的字段，例如：
            {
                "book_id": 1,
                "title": "书名",
                "author": "作者",
                "translator": "译者",
                "language": "中文",
                "intro": "图书简介...",
                "cover_url": "http://...",
                "good_rating": 0,
                "fair_rating": 0,
                "poor_rating": 0,
                "rating": 0.0,
                "rating_count": 0,
                "total_chapters": 20,
                "price": 29.8,
                "publish_time": "2024-01-01",
                "reading_count": 0,
            }
        """
        if not book_ids:
            return []

        # 构建 IN 子句的占位符
        placeholders = ", ".join(["%s"] * len(book_ids))
        where_clause = f"book_id IN ({placeholders})"

        # 调用父类的批量查询方法
        return await self.batch_query(
            where_clause=where_clause, params=tuple(book_ids), order_by=order_by
        )

    async def batch_query_books_with_categories(
        self, book_ids: List[int], order_by: str = "book_id ASC"
    ) -> List[Dict[str, Any]]:
        """
        根据批量 book_id 查询图书信息及其分类信息（合并后的完整数据）

        Args:
            book_ids (List[int]): 图书ID列表
            order_by (str): 排序子句，默认按 book_id 升序排列

        Returns:
            List[Dict[str, Any]]: 合并后的图书信息列表，每个字典包含：
            - 图书基本信息的所有字段
            - main_categories: 主分类列表
            - sub_categories: 子分类列表
            每个字典应包含图书的字段，例如：
            {
                "book_id": 1,
                "title": "书名",
                "author": "作者",
                "translator": "译者",
                "language": "中文",
                "intro": "图书简介...",
                "cover_url": "http://...",
                "good_rating": 0,
                "fair_rating": 0,
                "poor_rating": 0,
                "rating": 0.0,
                "rating_count": 0,
                "total_chapters": 20,
                "price": 29.8,
                "publish_time": "2024-01-01",
                "reading_count": 0,
                "main_categories": ["文学", "小说"],
                "sub_categories": ["当代小说", "经典小说"]
            }
        """
        if not book_ids:
            return []

        # 创建分类操作实例
        main_category_ops = MainCategoryOperations()
        sub_category_ops = SubCategoryOperations()

        # 并发查询所有数据
        books, main_categories, sub_categories = await asyncio.gather(
            self.batch_query_books_by_ids(book_ids, order_by),
            main_category_ops.batch_query_main_categories_by_book_ids(book_ids),
            sub_category_ops.batch_query_sub_categories_by_book_ids(book_ids),
        )

        # 构建分类字典，以book_id为键
        main_categories_dict = {}
        for category in main_categories:
            book_id = category["book_id"]
            if book_id not in main_categories_dict:
                main_categories_dict[book_id] = []
            main_categories_dict[book_id].append(category["main_category"])

        sub_categories_dict = {}
        for category in sub_categories:
            book_id = category["book_id"]
            if book_id not in sub_categories_dict:
                sub_categories_dict[book_id] = []
            sub_categories_dict[book_id].append(category["sub_category"])

        # 合并数据
        result = []
        for book in books:
            book_id = book["book_id"]
            # 添加分类信息到图书数据中
            book["main_categories"] = main_categories_dict.get(book_id, [])
            book["sub_categories"] = sub_categories_dict.get(book_id, [])
            result.append(book)

        return result
